﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace festivalDatabase
{
    /// <summary>
    /// This class allows for the Fine Artist gui to be created, managed, and edited.
    /// </summary>
    public partial class editFineArtist : userInterface
    {
        string oldName;
        string oldPhone;

        /// <summary>
        /// Initializes the GUI to create, edit, and delete a fine artist
        /// from the festival.
        /// </summary>
        /// <param name="db"> access the database </param>
        /// <param name="festivalNumber"> String representation of the selected festival </param>
        /// <param name="festivalID"> Actual festival ID </param>
        public editFineArtist(dbAccessor db, String festivalNumber, int festivalID)
        {
            InitializeComponent();
            dbAccess = db;
            setIcon();
            updateFineArtistList();
            currentFestivalID = festivalID;
            addArtistRadioButton.Checked = true;
        }

        /// <summary>
        /// Updates the fine artist list on database
        /// </summary>
        public void updateFineArtistList()
        {
            fineArtistListBox.Items.Clear();

            SQLiteCommand query = dbAccess.getCommand();

            query.CommandText = "SELECT name, telephone_number " +
                                "FROM fine_artist " +
                                "JOIN festival_has_fine_artist ON festival_has_fine_artist.fa_name=fine_artist.name AND festival_has_fine_artist.fa_phone=fine_artist.telephone_number;";
            try
            {
                festivals = dbAccess.executeQuery(query);
               // add name and telephone number (primary keys) to database
                foreach (DataRow row in festivals.Rows)
                {
                    fineArtistListBox.Items.Add((row["name"].ToString() + ", " + row["telephone_number"].ToString()));
                }
            }
            catch
            {
                fineArtistListBox.Items.Add("Error loading fine artists.");
            }
        }

        /// <summary>
        /// Closes the window.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cancelButton_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        
        /// <summary>
        /// Verifies that the keys for the artist (name and telephone) are unique
        /// when an artist is added or edited
        /// </summary>
        /// <returns> bool </returns>
        private bool KeysAreUnique()
        {
            if (artistNameTextBox.Text == "")
                return false;

            if (validNumLength(artistTelephoneNumber.Text, 10, false) == false)
                return false;

            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT name, telephone_number " +
                                "FROM fine_artist ;";
            DataTable dt = dbAccess.executeQuery(query);
            foreach (DataRow row in dt.Rows)  
            {
                string nameAndNum = row["name"].ToString() + ", " + row["telephone_number"].ToString();
                if (nameAndNum == artistNameTextBox.Text + ", " + artistTelephoneNumber.Text)
                {
                    return false;
                }
            }
            return true;
        }
       /// <summary>
       /// Checks to see whether an item in the fineArtistList box is selected. If so,
       /// updateFineArtistInfo and getAllFestivals are run
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
        private void fineArtistListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (fineArtistListBox.SelectedIndex >= 0)
            {
                if (addArtistRadioButton.Checked)
                    editArtistRadioButton.Checked = true;
                updateFineArtistInfo(fineArtistListBox.SelectedIndex);
                getAllFestivals(fineArtistListBox.SelectedIndex);
                oldName = artistNameTextBox.Text;
                oldPhone = artistTelephoneNumber.Text;
                removeArtistRadioButton.Enabled = true;
                scheduleArtistRadioButton.Enabled = true;
            }
            else
            {
                removeArtistRadioButton.Enabled = false;
                scheduleArtistRadioButton.Enabled = false;
            }
        }
        /// <summary>
        /// Queries the DB in order to update the artist on the database from
        /// the local entry fields.
        /// </summary>
        /// <param name="selectedIndex"> index value of the currently selected item</param>
        public void updateFineArtistInfo(int selectedIndex)
        {
            SQLiteCommand query = dbAccess.getCommand();

            query.CommandText = "SELECT name, telephone_number, artist_category, gallery " +
                                "FROM fine_artist;";
            try
            {
                festivals = dbAccess.executeQuery(query);
                {
                    DataRow row = festivals.Rows[selectedIndex];
                    artistNameTextBox.Text = (row["name"].ToString());
                    artistTelephoneNumber.Text = (row["telephone_number"].ToString());
                    artistCategory.Text = (row["artist_category"].ToString());
                    artistHomeGalleryLocation.Text = (row["gallery"].ToString());
                }
            }
            catch
            {
                fineArtistListBox.Items.Add("Error loading artist information.");
            }
        }
       /// <summary>
       /// Queries the DB in order to obtain all the festivals
       /// and organize them into a listbox and a dropbox, both of
       /// which are mutually exclusive.
       /// </summary>
       /// <param name="selectedIndex"> the item of the selected index</param>
        private void getAllFestivals(int selectedIndex)
        {
            string artistName = getArtistName();
            string phoneNum = getPhoneNum();

            fineArtistFestivalListBox.Items.Clear();
            fineArtistComboBox.Items.Clear();

            //current artist's festivals list box population
            SQLiteCommand query = dbAccess.getCommand();
            query.CommandText = "SELECT festival_id " +
                                "FROM festival_has_fine_artist " +
                                "WHERE fa_name=@name AND fa_phone=@phone;";
            SQLiteParameter param = new SQLiteParameter("@name", artistName);
            query.Parameters.Add(param);
            param = new SQLiteParameter("@phone", phoneNum);
            query.Parameters.Add(param);
            try
            {
                DataTable dt = dbAccess.executeQuery(query);
                foreach (DataRow row in dt.Rows)
                    fineArtistFestivalListBox.Items.Add(row["festival_id"].ToString());
            }
            catch
            {
                // do nothing!
            }

            //festivals drop box population
            query = dbAccess.getCommand();
            query.CommandText = "SELECT id " +
                                "FROM festival " +
                                "WHERE festival_type=@type EXCEPT " +
                                "SELECT festival_id " +
                                "FROM festival_has_fine_artist " +
                                "WHERE fa_name = @name AND fa_phone = @phone;";
            param = new SQLiteParameter("@type", "Art");
            query.Parameters.Add(param);
            param = new SQLiteParameter("@name", artistName);
            query.Parameters.Add(param);
            param = new SQLiteParameter("@phone", phoneNum);
            query.Parameters.Add(param);
            try
            {
                DataTable dt = dbAccess.executeQuery(query);
                foreach (DataRow row in dt.Rows)
                    fineArtistComboBox.Items.Add(row["id"].ToString());
            }
            catch
            {
                // do nothing!
            }
        }

        private string getArtistName()
        {
            string result = fineArtistListBox.SelectedItem.ToString();
            result = result.Substring(0, result.Length - 16);
            return result;
        }

        private string getPhoneNum()
        {
            string result = fineArtistListBox.SelectedItem.ToString();
            result = result.Substring(result.Length - 14, 14);
            return result;
        }

        /// <summary>
        /// Controls the event during a clear button click, which 
        /// clears all the data in the entry fields/text boxes
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void clearButton_Click(object sender, EventArgs e)
        {
            artistNameTextBox.Clear();
            artistTelephoneNumber.Clear();
            artistHomeGalleryLocation.Clear();
            artistCategory.Clear();
        }

        private void addArtistRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            dropBoxLabel.Visible = false;
            fineArtistComboBox.Visible = false;
            artistNameTextBox.Enabled = true;
            artistTelephoneNumber.Enabled = true;
            artistHomeGalleryLocation.Enabled = true;
            artistCategory.Enabled = true;
            clearButton.Visible = true;
            artistNameTextBox.Text = "";
            artistTelephoneNumber.Text = "";
            artistHomeGalleryLocation.Text = "";
            artistCategory.Text = "";
        }

        private void editArtistRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            dropBoxLabel.Visible = false;
            fineArtistComboBox.Visible = false;
            artistNameTextBox.Enabled = true;
            artistTelephoneNumber.Enabled = true;
            artistHomeGalleryLocation.Enabled = true;
            artistCategory.Enabled = true;
            clearButton.Visible = true;
        }

        private void removeArtistRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            dropBoxLabel.Visible = false;
            fineArtistComboBox.Visible = false;
            artistNameTextBox.Enabled = false;
            artistTelephoneNumber.Enabled = false;
            artistHomeGalleryLocation.Enabled = false;
            artistCategory.Enabled = false;
            clearButton.Visible = false;
        }

        private void scheduleArtistRadioButton_CheckedChanged(object sender, EventArgs e)
        {
            dropBoxLabel.Visible = true;
            fineArtistComboBox.Visible = true;
            artistNameTextBox.Enabled = false;
            artistTelephoneNumber.Enabled = false;
            artistHomeGalleryLocation.Enabled = false;
            artistCategory.Enabled = false;
            clearButton.Visible = false;
        }

        /// <summary>
        /// Decides which function is being performed, validates input, then
        /// calls the appropriate method to handle the submission.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void submitButton_Click(object sender, EventArgs e)
        {
            if (addArtistRadioButton.Checked)
            {
                if (KeysAreUnique())
                    addArtist();
                else
                    MessageBox.Show("Please fill the name and phone number boxes with unique values");
            }
            else if (editArtistRadioButton.Checked)
            {
                if (KeysAreUnique())
                    updateArtist();
                else
                    MessageBox.Show("Please fill the name and phone number boxes with unique values");
            }
            else if (removeArtistRadioButton.Checked)
            {
                removeArtist();
            }
            else // schedule artist for festival is checked
            {
                if (fineArtistComboBox.Text != "")
                    scheduleArtistForFestival();
                else
                    MessageBox.Show("Please select a festival");
            }
        }

        /// <summary>
        /// Clears the input fields.
        /// </summary>
        private void clearInputFields()
        {
            artistNameTextBox.Text = "";
            artistTelephoneNumber.Text = "";
            artistCategory.Text = "";
            artistHomeGalleryLocation.Text = "";
        }

        /// <summary>
        /// Adds a new artist to the database.
        /// </summary>
        private void addArtist()
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "INSERT INTO fine_artist (" +
                                   "name, telephone_number";
            if (artistCategory.Text != "")
                nonQuery.CommandText += ", artist_category";
            if (artistHomeGalleryLocation.Text != "")
                nonQuery.CommandText += ", gallery";
            nonQuery.CommandText += ") VALUES (@name, @phone";
            if (artistCategory.Text != "")
                nonQuery.CommandText += ", @cat";
            if (artistHomeGalleryLocation.Text != "")
                nonQuery.CommandText += ", @gallery";
            nonQuery.CommandText += ");";

            SQLiteParameter param = new SQLiteParameter("@name", artistNameTextBox.Text);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@phone", artistTelephoneNumber.Text);
            nonQuery.Parameters.Add(param);
            if (artistCategory.Text != "")
            {
                param = new SQLiteParameter("@cat", artistCategory.Text);
                nonQuery.Parameters.Add(param);
            }
            if (artistHomeGalleryLocation.Text != "")
            {
                param = new SQLiteParameter("@gallery", artistHomeGalleryLocation.Text);
                nonQuery.Parameters.Add(param);
            }

            dbAccess.executeNonQuery(nonQuery);
            System.Windows.Forms.MessageBox.Show("Fine Artist added successfully.");
            clearInputFields();
            updateFineArtistList();
        }

        /// <summary>
        /// Updates an existing artist in the database.
        /// </summary>
        private void updateArtist()
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "UPDATE fine_artist (" +
                                   "name, telephone_number";
            if (artistCategory.Text != "")
                nonQuery.CommandText += ", artist_category";
            if (artistHomeGalleryLocation.Text != "")
                nonQuery.CommandText += ", gallery";
            nonQuery.CommandText += ") VALUES (@name, @phone";
            if (artistCategory.Text != "")
                nonQuery.CommandText += ", @cat";
            if (artistHomeGalleryLocation.Text != "")
                nonQuery.CommandText += ", @gallery";
            nonQuery.CommandText += ") WHERE name=@oldName AND telephone_number=@oldPhone;";

            SQLiteParameter param = new SQLiteParameter("@name", artistNameTextBox.Text);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@phone", artistTelephoneNumber.Text);
            nonQuery.Parameters.Add(param);
            if (artistCategory.Text != "")
            {
                param = new SQLiteParameter("@cat", artistCategory.Text);
                nonQuery.Parameters.Add(param);
            }
            if (artistHomeGalleryLocation.Text != "")
            {
                param = new SQLiteParameter("@gallery", artistHomeGalleryLocation.Text);
                nonQuery.Parameters.Add(param);
            }

            param = new SQLiteParameter("@oldName", oldName);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@oldPhone", oldPhone);
            nonQuery.Parameters.Add(param);

            dbAccess.executeNonQuery(nonQuery);
            System.Windows.Forms.MessageBox.Show("Fine Artist edited successfully.");
            updateFineArtistList();
        }

        private void removeArtist()
        {
            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "DELETE FROM fine_artist " +
                                   "WHERE name=@name AND telephone_number=@phone;";
            SQLiteParameter param = new SQLiteParameter("@name", artistNameTextBox.Text);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@phone", artistTelephoneNumber.Text);
            nonQuery.Parameters.Add(param);

            dbAccess.executeNonQuery(nonQuery);
            updateFineArtistList();
            MessageBox.Show("Artist Removed");
            artistNameTextBox.Text = "";
            artistTelephoneNumber.Text = "";
            artistHomeGalleryLocation.Text = "";
            artistCategory.Text = "";
        }

        private void scheduleArtistForFestival()
        {
            int festivalNum = Int32.Parse(fineArtistComboBox.Text);

            SQLiteCommand nonQuery = dbAccess.getCommand();
            nonQuery.CommandText = "INSERT INTO festival_has_fine_artist " +
                                   "VALUES (@id, @name, @phone);";
            SQLiteParameter param = new SQLiteParameter("@id", festivalNum);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@name", artistNameTextBox.Text);
            nonQuery.Parameters.Add(param);
            param = new SQLiteParameter("@phone", artistTelephoneNumber.Text);
            nonQuery.Parameters.Add(param);

            dbAccess.executeNonQuery(nonQuery);
            MessageBox.Show("Artist added to festival " + festivalNum);
            getAllFestivals(fineArtistListBox.SelectedIndex);
            fineArtistComboBox.Text = "";
        }

        private void fineArtistFestivalRemove_Click(object sender, EventArgs e)
        {
            if (fineArtistFestivalListBox.SelectedIndex >= 0)
            {
                SQLiteCommand nonQuery = dbAccess.getCommand();
                nonQuery.CommandText = "DELETE FROM festival_has_fine_artist " +
                                       "WHERE festival_id=@id AND fa_name=@name AND fa_phone=@phone;";
                int festNum = Int32.Parse(getFestivalNumber(fineArtistFestivalListBox.SelectedItem.ToString()));
                SQLiteParameter param = new SQLiteParameter("@id", festNum);
                nonQuery.Parameters.Add(param);
                param = new SQLiteParameter("@name", artistNameTextBox.Text);
                nonQuery.Parameters.Add(param);
                param = new SQLiteParameter("@phone", artistTelephoneNumber.Text);
                nonQuery.Parameters.Add(param);

                dbAccess.executeNonQuery(nonQuery);
                MessageBox.Show("Removed artist from festival");
                getAllFestivals(fineArtistListBox.SelectedIndex);
            }
            else
                MessageBox.Show("Please select a festival to remove the artist from");
        }
    }
}
